/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Dao;
import Entidad.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author ANDREZ
 */
public class RrhhDao {
    
    private static RrhhDao INSTANCE = new RrhhDao();
    private RrhhDao() {
    }
    public static RrhhDao getInstance(){
    return INSTANCE;
    }

    public CredentialBE InicioSesion(String usuario, String password)
    {
        List<NotaBE.TipoNotaBE> lista = new ArrayList();
        String sql ="{call Credencial_SelectPorNombreYPassword_pa(?,?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
         CredentialBE cc = null;
        try {
            cs = c.prepareCall(sql);
            cs.setString(1, usuario);
            cs.setString(2, password);
            rs = cs.executeQuery();
            while(rs.next())
            {
                 cc = new CredentialBE();
                  cc.setId(rs.getInt("IdCredencial"));
                  cc.setUsuario(rs.getString("Usuario"));
                  BaseBE tipo = new BaseBE();
                  tipo.setId(rs.getInt("IdTipo"));
                  tipo.setDescripcion(rs.getString("Descripcion"));
                  cc.setTipo(tipo);
                  PersonaBE persona = new AlumnoBE();
                  persona.setApellidos(rs.getString("Apellidos"));
                  persona.setNombrepersona(rs.getString("Nombres"));
                  persona.setFechanacimiento(rs.getDate("FechaNacimiento"));
                    persona.setNrodocumento(rs.getString("NroDocumento"));
                  cc.setPersona(persona);
            }
        } catch (SQLException ex) {
            Logger.getLogger(NotaDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return  cc;
    }
    
    public List<BaseBE> listaGenero()
    {
        List<BaseBE> lista = new ArrayList();
        String sql ="{call sp_listaTipos_Gino(?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1,4);
            rs = cs.executeQuery();
            while(rs.next())
            {
                BaseBE obj = new BaseBE();
                obj.setId(rs.getInt("IdEnum"));
                obj.setNombre(rs.getString("Descripcion"));
               lista.add(obj);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MatriculaDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return  lista;        
    }
    
    public List<BaseBE> listaTipoTelefono()
    {
        List<BaseBE> lista = new ArrayList();
        String sql ="{call sp_listaTipos_Gino(?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1,2);
            rs = cs.executeQuery();
            while(rs.next())
            {
                BaseBE obj = new BaseBE();
                obj.setId(rs.getInt("IdEnum"));
                obj.setNombre(rs.getString("Descripcion"));
               lista.add(obj);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MatriculaDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return  lista;
    }
    
    public List<BaseBE> listaOperador()
    {
        List<BaseBE> lista = new ArrayList();
        String sql ="{call sp_listaTipos_Gino(?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1,5);
            rs = cs.executeQuery();
            while(rs.next())
            {
                BaseBE obj = new BaseBE();
                obj.setId(rs.getInt("IdEnum"));
                obj.setNombre(rs.getString("Descripcion"));
               lista.add(obj);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MatriculaDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return  lista;
    }
    
    public List<BaseBE> listaTipoContrato()
    {
        List<BaseBE> lista = new ArrayList();
        String sql ="{call sp_listaTipos_Gino(?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        ResultSet rs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1,11);
            rs = cs.executeQuery();
            while(rs.next())
            {
                BaseBE obj = new BaseBE();
                obj.setId(rs.getInt("IdEnum"));
                obj.setNombre(rs.getString("Descripcion"));
               lista.add(obj);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MatriculaDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return  lista;
    }
        
    
    public EmpleadoBE listaEmpleadoPorDNI(String dni)
    {
        EmpleadoBE e = new EmpleadoBE();
        String sql ="{call sp_BuscarEmpleadoPorDNI(?)}";
        Connection c = new bd().getConnectionSQL();
        CallableStatement cs;
        ResultSet rs;
        try {
            cs = c.prepareCall(sql);
            cs.setString(1, dni);
            rs = cs.executeQuery();
            while(rs.next())
            {
                
                BaseBE b1=new BaseBE();
                BaseBE b2=new BaseBE();
                BaseBE b3=new BaseBE();
                BaseBE b4=new BaseBE();
                BaseBE b=new BaseBE();
                TelefonoBE t= new TelefonoBE();
                e.setId(rs.getInt("IdPersona"));
                e.setNrodocumento(rs.getString("NroDocumento"));
                e.setNombrepersona(rs.getString("Nombres"));
                e.setApellidos(rs.getString("Apellidos"));
                e.setFechanacimiento(rs.getDate("FechaNacimiento"));
                b1.setNombre(rs.getString("DireccionPersona"));
                e.setDireccion(b1);
                b2.setId(rs.getInt("IdGenero"));
 
                b3.setId(rs.getInt("TipoTelefonoPersona"));
                //p.setTipo(b3);x
                b4.setId(rs.getInt("OperadorPersona"));
                t.setOperador(b4);
                e.setTelefono(t);
                t.setNumero(rs.getString("NumeroTelefonoPersona"));
                e.setTelefono(t);
                
                
                e.setCargahoraria(rs.getInt("CargaHoraria"));
                e.setFechaingreso(rs.getDate("FechaIngreso"));
                
                e.setId(rs.getInt("IdTipoContrato"));
                e.setTipo(b);
                e.setGenero(b2);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MatriculaDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return e;
    }
    
    
    public Boolean  Grabar(EmpleadoBE e)
    {
        Boolean exito= false;
        String sql ="{call Empleado_Insertar_pa(?,?,?,?,?,?,?,?,?,?,?,?)}";
        Connection c = new bd().getConnectionSQL();
       CallableStatement cs;
        try {
            cs = c.prepareCall(sql);
            cs.setInt(1 ,e.getId());
            cs.setString(2, e.getApellidos());
            cs.setString(3, e.getNombre());
            cs.setString(4, e.getNrodocumento());
            cs.setString(5, e.getTelefono().getNombre()); 
            cs.setInt(6, e.getTelefono().getOperador().getId());
            cs.setInt(7, e.getTelefono().getTipo().getId());
            cs.setString(8, e.getDireccion().getNombre());
            cs.setString(9, e.getContrado().getNombre());
            cs.setInt(10, e.getCargahoraria());
            cs.setString(11, e.getGenero().getNombre());
            cs.setInt(12, e.getIdtipocontrato());
 
          
           exito=true;
            
        } catch (SQLException ex) {
            Logger.getLogger(RrhhDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return exito;
    }    
    
}
